Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Table of Contents


Appendix B
Quick Reference

This appendix contains quick summaries you can use to refresh your memory on some of the areas covered in this book.

Oracle Instance Tuning

This section reviews Oracle instance tuning: where to look for performance information in the dynamic performance tables as well as a review of tuning guidelines.

Library Cache

The V$LIBRARYCACHE table contains statistics on how well you are using the library cache. The important columns to view in this table are PINS and RELOADS:

PINS The number of times the item in the library cache was executed.
RELOADS The number of times the library cache missed and the library object was reloaded.
     Cache Miss Rate = SUM(RELOADS)/SUM(PINS)

SQL Statements

Total Miss Percent:

SELECT SUM(reloads) "Cache Misses",
SUM(pins) "Executions",
100 * ( SUM(reloads) / SUM(pins) ) "Cache Miss Percent"
FROM v$librarycache;

Individual Statistics:

SELECT namespace,
reloads "Cache Misses",
pins "Executions"
FROM v$librarycache;

Data Dictionary Cache

Statistics for the data dictionary cache are stored in the dynamic performance table V$ROWCACHE (the data dictionary cache is sometimes known as the row cache). The important columns to view in this table are GETS and GETMISSES:

GETS The total number of requests for the particular item.
GETMISSES The total number of requests resulting in cache misses.
     Cache Miss Rate = SUM(GETMISSES)/SUM(GETS)

SQL Statements

Total Miss Percent:

SELECT SUM(getmisses) "Cache Misses",
SUM(gets) "Requests",
100 * ( SUM(getmisses) / SUM(gets) ) "Cache Miss Percent"
FROM v$rowcache;

Individual Statistics:

SELECT parameter,
getmisses "Cache Misses",
gets "Requests"
FROM v$rowcache;

Database Buffer Cache

The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT. The important columns to view in this table are PHYSICAL READS, DB BLOCK GETS, and CONSISTENT GETS:

PHYSICAL READS The total number of requests that result in a disk access. This is a cache miss.
DB BLOCK GETS The number of requests for blocks in current mode.
CONSISTENT GETS The number of requests for blocks in consistent mode. A consistent-mode request is one that is satisfied from a rollback record for consistency.
     Cache Hit Ratio = 1 - PHYSICAL READS / ( DB BLOCK GETS + CONSISTENT GETS )

SQL Statement

Block Buffer Information:

SELECT name, value
FROM v$sysstat
WHERE name IN ('db block gets', 'consistent gets', 'physical reads');

Remember: DB Block Buffer Size = DB_BLOCK_BUFFERS * DB_BLOCK_SIZE

Physical I/O Usage

Information about disk accesses is kept in the dynamic performance table V$FILESTAT. Important information in this table is found in the following columns:

PHYRDS The number of physical reads done to the data file.
PHYWRTS The number of physical writes done to the data file.

The information in V$FILESTAT is referenced by file number. The dynamic performance table V$DATAFILE contains a reference to this number as well as this useful information:

NAME The name of the data file.
STATUS The type of file and its current status.
BYTES The size of the data file.

SQL Statement

I/O Information:

SELECT SUBSTR(name,1,40), phyrds, phywrts, status, bytes
FROM  v$datafile  df,  v$filestat  fs
WHERE df.file# = fs.file#;

Remember that disks have certain inherent limitations that cannot be exceeded.

Disk I/O Review

Sequential I/O Data is written to or read from the disk in order; very little head movement occurs. Access to the redo log files is always sequential.
Random I/O Data is accessed in different places on the disk; lots of head movement occurs. Access to the data files is almost always random. For database loads, access is sequential; in most other cases (especially OLTP), access patterns are almost always random.

Disk I/O Rates

Remember these general limitations (and refer to Chapter 14, “Advanced Disk I/O Concepts”):

Sequential I/O A typical SCSI-II disk drive can support approximately 100 to 150 sequential I/Os per second.
Random I/O A typical SCSI-II disk drive can support approximately 50 to 60 random I/Os per second.

I/O Rules of Thumb

Isolate sequential I/Os Because sequential I/Os can occur at a much higher rate, isolating them lets you run these drives much faster.
Spread out random I/Os You can accomplish this goal by striping table data using Oracle striping, OS striping, or hardware striping.
Separate data and indexes By separating a heavily used table from its index, you allow a query to a table to access data and indexes on separate disks simultaneously.
Reduce non-Oracle I/O Eliminate non-Oracle disk I/O from disks that contain database files. Any other disk I/O slows down Oracle’s access to these disks.

Chained Rows

You can check for chained rows with the ANALYZE command’s LIST CHAINED ROWS option. Use these SQL statements to check for chained or migrated rows:

Rem Create the Chained Rows Table
Rem
CREATE TABLE chained_rows (
owner_name    varchar2(30),
table_name    varchar2(30),
cluster_name  varchar2(30),
head_rowid    rowid,
timestamp     date);
Rem
Rem Analyze the Table in Question
Rem
ANALYZE
    TABLE scott.emp LIST CHAINED ROWS;
Rem
Rem Check the Results
Rem
SELECT * from chained_rows;

Recursive Calls

You can check the number of recursive calls through the dynamic performance table V$SYSSTAT. Use the following command:

SELECT name, value
FROM v$SYSSTAT
WHERE name = 'recursive calls';

Rollback Segment Contention

You can tell whether you are seeing contention on rollback segments by looking at the dynamic performance table V$WAITSTAT. V$WAITSTAT contains the following data related to rollback segments:

UNDO HEADER The number of waits for buffers containing rollback header blocks.
UNDO BLOCK The number of waits for buffers containing rollback blocks other than header blocks.
SYSTEM UNDO HEADER Same as UNDO HEADER for the SYSTEM rollback segment.
SYSTEM UNDO BLOCK Same as UNDO BLOCK for the SYSTEM rollback segment.

SQL Statement

Rollback Information:

SELECT class, count
FROM V$WAITSTAT
WHERE class IN
('undo header', 'undo block', 'system undo header', 'system undo block');

Dynamic Rollback Growth

To determine whether dynamic growth of rollback segments is a problem, look in the dynamic performance table V$ROLLSTAT. The following columns are of particular interest:

EXTENTS Number of rollback extents.
RSSIZE The size in bytes of the rollback segment.
OPTSIZE The size that the OPTIMAL parameter was set to.
AVEACTIVE The current average size of active extents. Here active extents are extents with uncommitted transaction data.
AVESHRINK The total size of free extents divided by the number of shrinks.
EXTENDS The number of times the rollback segment added an extent.
SHRINKS The number of times the rollback segment shrank. This shrink may be one or more extents at a time.
HWMSIZE The high-water mark of rollback segment size. This is the largest that the segment ever grew to be.

SQL Statement

Rollback Dynamic Growth Information:

SELECT substr(name, 1,40), extents, rssize, aveactive, aveshrink, extends, shrinks
FROM v$rollname rn, v$rollstat rs
WHERE rn.usn = rs.usn;

Redo Log Buffer Contention

Information concerning redo log buffer contention is stored in the dynamic performance table V$SYSSTAT as the value of the redo log space requests entry. If this number is not zero, it means that a process had to wait for space in the redo log buffer; you should increase its size. Check for this condition with the following SQL statement.

SQL Statement

Redo Log Buffer Contention Information:

SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';

Redo Latch Contention

Latch contention can be determined by examining the dynamic performance table V$LATCH. The significant values are described here:

GETS For willing-to-wait requests. The number of successful requests.
MISSES For willing-to-wait requests. The number of times the initial request failed.
SLEEPS For willing-to-wait requests. The number of times subsequent requests failed.
IMMEDIATE_GETS For immediate requests. The number of successful requests.
IMMEDIATE_MISSES For immediate requests. The number of times the request failed.

SQL Statement

Redo Latch Contention Information:

SELECT SUBSTR(name,1,20), gets, misses, sleeps, immediate_gets, immediate_misses
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy');

Sort Performance

One way to tell whether your sorts are occurring in memory or on disk is to look in the dynamic performance table V$SYSSTAT. The statistics of interest are give here:

sorts (memory) The number of sorts that were able to fit in the in-memory sort area.
sorts (disk) The number of sorts that required temporary space on disk.

SQL Statement

Sort Performance Information:

SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');

Free List Contention

Contention on the free list can be determined by looking at the dynamic performance table V$WAITSTAT using the following SQL statement.

SQL Statement

Free List Contention Information:

SELECT class, count
FROM v$waitstat
WHERE class = 'free list';


Table of Contents


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.